/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ExcelAccess;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.StringTokenizer;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FontUnderline;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 *
 * @author workshop
 */
public class ExcelHandler {
    String fileName = null;
    public ArrayList<String> objectContent = null;
    public ArrayList<String> serviceCallsContent = null;
    
    public ExcelHandler(String fileName){
        this.fileName = fileName;
    }
    
    public ExcelHandler(ArrayList<String> objectContent,ArrayList<String> serviceCallsContent){
        this.objectContent = objectContent;
        this.serviceCallsContent = serviceCallsContent;
    }
    
    public void readContent() throws FileNotFoundException, IOException, Exception {

        FileInputStream fileInputStream = new FileInputStream(fileName);
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);

        XSSFSheet sheet = workbook.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;
        int rid = 0;
        int cid = 0;
        boolean found = false;
        for (rid = 0; rid < sheet.getPhysicalNumberOfRows(); rid++) {
            if ((row = sheet.getRow(rid)) != null) {
                for (cid = 0; cid < row.getPhysicalNumberOfCells(); cid++) {
                    if ((cell = row.getCell(cid)) != null) {
                        if (getCellString(cell).equalsIgnoreCase("Object")) {
                            found = true;
                            break;
                        }
                    }
                }
                if (found) break;
            }
        }
        if (!found) throw new Exception("excel file format wrong!");
        

        objectContent = new ArrayList<>();
        for (int m = 0; m < ExcelFormat.objectsFields.length; m++) {
            if ((rid + m) >= sheet.getPhysicalNumberOfRows()) {
                throw new Exception("excel file format wrong!");
            }
            row = sheet.getRow(rid + m);
            if (row == null) {
                throw new Exception("excel file format wrong!");
            }
            if ((cid + 1) >= row.getPhysicalNumberOfCells()) {
                throw new Exception("excel file format wrong!");
            }
            cell = row.getCell(cid + 1);
            if (cell == null) {
                throw new Exception("excel file format wrong!");
            }
            String input = getCellString(cell);
            if(input.isEmpty() && m == 0) throw new Exception("Object name cannot be empty!");
            objectContent.add(input);
        }
        
        serviceCallsContent = new ArrayList<>();    
        found = false;
        rid = rid + ExcelFormat.objectsFields.length;
        for(;rid < sheet.getPhysicalNumberOfRows(); rid++){
            if ((row = sheet.getRow(rid)) != null) {
                for (cid = 0; cid < row.getPhysicalNumberOfCells(); cid++) {
                    if ((cell = row.getCell(cid)) != null) {
                        if (getCellString(cell).equalsIgnoreCase("Service No.")) {
                            found = true;
                            break;
                        }
                    }
                }
                if (found) break;
            }
        }
        
        if(!found) throw new Exception("no service call inputs!");
        
        rid++;
          while(rid < sheet.getPhysicalNumberOfRows()){
            row = sheet.getRow(rid);
            if(row == null) throw new Exception("excel file format wrong!");
            cell = row.getCell(cid);
            if(cell == null) break;
            String serviceNo = getCellString(cell);
            if(serviceNo.isEmpty()) break;
            
            if((cid + ExcelFormat.serviceCallFields.length - 1) > row.getPhysicalNumberOfCells())
                throw new Exception("excel file format wrong!");
            
            serviceCallsContent.add(objectContent.get(0));
            for(int m = cid; m < cid + ExcelFormat.serviceCallFields.length - 1; m++){
                cell = row.getCell(m);
                if (cell == null) throw new Exception("excel file format wrong!");
                String input = getCellString(cell);
                if(ExcelFormat.serviceCallFields[m - cid + 1].equalsIgnoreCase("Date")){
                    if(!input.isEmpty()){
                        input = formatDate(input);
                    }
                }
                serviceCallsContent.add(input);
            }
            rid++;
        }
        
    }
    
    private String formatDate(String input) throws Exception{
        SimpleDateFormat parserSDF = null;
        Date inputDate = null;
        try{
            parserSDF = new SimpleDateFormat("EEE MMM d HH:mm:ss zzz yyyy");
            inputDate = parserSDF.parse(input);
            parserSDF = new SimpleDateFormat("dd/MM/yyyy");
            return parserSDF.format(inputDate);
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        try{
            parserSDF = new SimpleDateFormat("dd/MM/yyyy");
            inputDate = parserSDF.parse(input);
            return parserSDF.format(inputDate);
        }catch(Exception e){
             throw new Exception("Date value: "+inputDate + " cannot recognized");
        }                
            
    }
    
    public void writeContent(File f) throws Exception{
        FileOutputStream fileOutputStream = new FileOutputStream(f);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        XSSFSheet sheet = xssfWorkbook.createSheet("Service Report");
        int filterRow = -1;
        for(int i = 0; i < ExcelFormat.serviceCallFields.length; i++) sheet.setColumnWidth(i, 8000);
        
        int pictureIdx = -1;
        try{
            InputStream is = new FileInputStream("huisman_welcome.jpeg");
            byte[] bytes = IOUtils.toByteArray(is);
            pictureIdx = xssfWorkbook.addPicture(bytes, XSSFWorkbook.PICTURE_TYPE_JPEG);
            is.close();
        }catch(Exception e){
            System.out.println(e.getMessage());
        }
        int row = 0;
        XSSFRow rowData = null;
        for(int i = 0; i < 7; i++){
            rowData = sheet.createRow(row);
            row++;
        }
        if(pictureIdx >= 0){
            Drawing drawing = sheet.createDrawingPatriarch();
            CreationHelper helper = xssfWorkbook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(0);
            anchor.setRow1(0);
            Picture pict = drawing.createPicture(anchor, pictureIdx);
            pict.resize();
        }
        
        
        rowData = sheet.createRow(row);
        XSSFCell bigHeader = rowData.createCell(0);
        bigHeader.setCellStyle(this.getCellStyle(xssfWorkbook, 20, "Calibri", true, FontUnderline.SINGLE,false));
        bigHeader.setCellValue("HISTORY SHEET");
        row++;
        
        rowData = sheet.createRow(row);
        row++;
        
        for(int i = 0; i < ExcelFormat.objectsFields.length; i++){
            rowData = sheet.createRow(row);
            XSSFCell cell0 = rowData.createCell(0);
            XSSFCell cell1 = rowData.createCell(1);
            
            cell0.setCellStyle(this.getCellStyle(xssfWorkbook, 14, "Calibri", true, FontUnderline.NONE,true));
            cell0.setCellValue(ExcelFormat.objectsFields[i]);
            cell1.setCellStyle(this.getCellStyle(xssfWorkbook, 14, "Calibri", false, FontUnderline.NONE,true));
            cell1.setCellValue(objectContent.get(i));
            row++;
        }
        
        rowData = sheet.createRow(row);
        row++;
        
        rowData = sheet.createRow(row);
        XSSFCell serviceCallHeader = rowData.createCell(0);
        serviceCallHeader.setCellStyle(this.getCellStyle(xssfWorkbook, 16, "Calibri", true, FontUnderline.NONE,false));
        serviceCallHeader.setCellValue("Service Call Records");
        row++;
        
        // start of service records
        rowData = sheet.createRow(row);
        filterRow = row+1;
        int numOfRecords = serviceCallsContent.size() / ExcelFormat.serviceCallFields.length;
        
        for(int i = 0; i < ExcelFormat.serviceCallFields.length-1; i++){
            XSSFCell cell = rowData.createCell(i);
            cell.setCellStyle(this.getCellStyle(xssfWorkbook, 11, "Calibri", true, FontUnderline.NONE,true));
            cell.setCellValue(ExcelFormat.serviceCallFields[i+1]);
        }
        row++;
        
        
        for(int i = 0; i < numOfRecords; i++){
            rowData = sheet.createRow(row);
            for(int j = 0; j < ExcelFormat.serviceCallFields.length-1; j++){
                 XSSFCell cell = rowData.createCell(j);
                 String value = serviceCallsContent.get(i*ExcelFormat.serviceCallFields.length+j+1);
                 cell.setCellStyle(this.getCellStyle(xssfWorkbook, 11, "Calibri", false, FontUnderline.NONE,true));
                 cell.setCellValue(value);
                 
                 
            }
            row++;
        }
        sheet.setAutoFilter(CellRangeAddress.valueOf("A"+filterRow+":W"+(numOfRecords+filterRow)));
        xssfWorkbook.write(fileOutputStream);
    }
    
    public String getCellString(XSSFCell cell) {
        String result = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                result += cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    result += cell.getDateCellValue();
                } else {
                    result += cell.getNumericCellValue();
                }
                break;
        }
        StringTokenizer st = new StringTokenizer(result,"'");
        result = "";
        if(!st.hasMoreTokens()) return result;
        result += st.nextToken();
        while(st.hasMoreTokens()){
            result += "\\'";
            result += st.nextToken();
        }
        return result.trim();
    }
    
    private XSSFCellStyle getCellStyle(XSSFWorkbook xssfWorkbook,int fontSize,String fontName,boolean ifBold,FontUnderline fontUnderLine,boolean border){
         XSSFCellStyle style = xssfWorkbook.createCellStyle();
         XSSFFont objectFont = xssfWorkbook.createFont();
         objectFont.setFontHeight(fontSize);
         objectFont.setFontName(fontName);
         objectFont.setBold(ifBold);
         objectFont.setUnderline(fontUnderLine);
         style.setFont(objectFont);
         style.setWrapText(true);
         style.setVerticalAlignment(VerticalAlignment.TOP);
         if(border){
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
         }
         return style;
    }
    
    private XSSFCellStyle getHyperlinkStyle(XSSFWorkbook xssfWorkbook,int fontSize,String fontName){
         XSSFCellStyle style = xssfWorkbook.createCellStyle();
         XSSFFont objectFont = xssfWorkbook.createFont();
         objectFont.setFontHeight(fontSize);
         objectFont.setFontName(fontName);
         objectFont.setUnderline(Font.U_SINGLE);
         objectFont.setColor(IndexedColors.BLUE.getIndex());
         style.setFont(objectFont);
         return style;
    }
    
}
